﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient ;
using System.Windows.Forms;
using System.Data;

namespace 提交管理平台V1
{
    public class SqlTools
    {
        private string _strConnectionString;
        private SqlConnection _Conn;
        private string _ErrMessage;
        private bool _ShowErrMessages = true;

        string strSelectServerDate = "select CONVERT(varchar(12) , getdate(), 112)";        

        //构造函数
        public SqlTools(string strTmpConnectionString)
        {
            ConnectionString = strTmpConnectionString;
            iniConnection();
        }

        #region 属性
        //连接字符串
        public string ConnectionString
        {
            set
            {
                _strConnectionString = value;
            }
            get
            {
                return _strConnectionString;
            }
        }

        //连接状态
        public ConnectionState ConnectionState
        {
            get
            {
                return _Conn.State;
            }
        }

        //获取错误消息
        public string ErrMessage
        {
            get
            {
                return _ErrMessage;
            }
        }

        //是否显示错误消息
        public bool ShowErrMessages
        {
            set
            {
                _ShowErrMessages = value;
            }
            get
            {
                return _ShowErrMessages;
            }
        }

        //获取连接
        public SqlConnection GetConnection()
        {
            return _Conn;
        }
        #endregion 属性

        #region 方法
        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        private void iniConnection()
        {
            _Conn = new SqlConnection(_strConnectionString);
            try
            {
                OpenConnection();
                CloseConnection();
            }
            catch(Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        private void OpenConnection()
        {
            try
            {
                _Conn.Open();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        private void CloseConnection()
        {
            try
            {
                _Conn.Close();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <returns>查询结果</returns>
        public DataTable GetData(string strTmpSelectString)
        {
            DataTable tmpdt = new DataTable();
            SqlDataAdapter tmpsda = new SqlDataAdapter(strTmpSelectString, _Conn);
            try
            {
                OpenConnection();
                tmpsda.Fill(tmpdt);
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
            finally
            {
                CloseConnection();
            }
            return tmpdt;
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <returns>查询结果</returns>
        public DataSet GetDataSet(string strTmpSelectString)
        {
            DataSet tmpds = new DataSet();
            SqlDataAdapter tmpsda = new SqlDataAdapter(strTmpSelectString, _Conn);
            try
            {
                OpenConnection();
                tmpsda.Fill(tmpds);
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
            finally
            {
                CloseConnection();
            }
            return tmpds ;
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="strTmpCmdString">命令语句</param>
        /// <returns>执行记录条数</returns>
        public int DoCmdExecuteNonQuery(string strTmpCmdString)
        {
            SqlCommand tmpcmd = new SqlCommand(strTmpCmdString, _Conn);
            int r=0;
            try
            {
                OpenConnection();
                r = tmpcmd.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
            finally
            {
                CloseConnection();
            }
            return r;
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="strTmpCmdString">命令语句</param>
        /// <returns>执行记录条数</returns>
        public int DoCmdExecuteNonQuery(SqlCommand tmpcmd)
        {
            tmpcmd.Connection = _Conn;
            int r = 0;
            try
            {
                OpenConnection();
                r = tmpcmd.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
            finally
            {
                CloseConnection();
            }
            return r;
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="strTmpCmdString">查询语句</param>
        /// <returns>查询结果的第一行第一列</returns>
        public Object DoCmdScalarData(string strTmpCmdString)
        {
            SqlCommand tmpcmd = new SqlCommand(strTmpCmdString, _Conn);
            Object r = null ;
            try
            {
                OpenConnection();
                r = tmpcmd.ExecuteScalar() ;
                CloseConnection();
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
            }
            finally
            {
                CloseConnection();
            }
            return r;
        }

        /// <summary>
        /// 更新数据表，提交数据更改
        /// </summary>
        /// <param name="strTmpSelectString">数据表查询语句</param>
        /// <param name="tmpdt">包含更改的数据表</param>
        /// <returns>提交是否成功</returns>
        public bool UpdateChanges(string strTmpSelectString, DataTable tmpdt)
        {
            SqlDataAdapter tmpsda = new SqlDataAdapter(strTmpSelectString,_Conn );
            SqlCommandBuilder tmpscb = new SqlCommandBuilder(tmpsda);
            tmpsda.InsertCommand = tmpscb.GetInsertCommand(true);
            tmpsda.DeleteCommand = tmpscb.GetDeleteCommand(true);
            tmpsda.UpdateCommand = tmpscb.GetUpdateCommand(true);
            try
            {
                OpenConnection();
                tmpsda.Update(tmpdt);
                return true;
            }
            catch (Exception ex)
            {
                _ErrMessage = ex.Message;
                if (_ShowErrMessages)
                {
                    ShowErrMsg(ex.Message);
                }
                return false;
            }
            finally
            {
                CloseConnection();
            }
        }
        
        /// <summary>
        /// 填充下拉列表框
        /// </summary>
        /// <param name="cmb">要填充的ComboBox控件</param>
        /// <param name="strTmpSelectString">查询语句</param>
        public void FillComboBox(ComboBox cmb, string strTmpSelectString)
        {
            DataTable tmpdt = GetData(strTmpSelectString);
            cmb.Items.Clear();
            if (tmpdt.Rows.Count > 0)
            {
                for (int i = 0; i < tmpdt.Rows.Count; i++)
                {
                    if (tmpdt.Rows[i][0].Equals(DBNull.Value))
                    {
                        cmb.Items.Add("[NULL]");
                    }
                    else
                    {
                        cmb.Items.Add(tmpdt.Rows[i][0]);
                    }
                }
            }
        }

        /// <summary>
        /// 填充列表框
        /// </summary>
        /// <param name="lst">要填充的ListBox控件</param>
        /// <param name="strTmpSelectString">查询语句</param>
        public void FillListBox(ListBox lst, string strTmpSelectString)
        {
            DataTable tmpdt = GetData(strTmpSelectString);
            lst.Items.Clear();
            if (tmpdt.Rows.Count > 0)
            {
                for (int i = 0; i < tmpdt.Rows.Count; i++)
                {
                    if (tmpdt.Rows[i][0].Equals(DBNull.Value))
                    {
                        lst.Items.Add("[NULL]");
                    }
                    else
                    {
                        lst.Items.Add(tmpdt.Rows[i][0]);
                    }
                }
            }
        }

        /// <summary>
        /// 填充下拉列表框
        /// </summary>
        /// <param name="cmb">要填充的ComboBox控件</param>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <param name="iColumnIndex">要填充的列索引</param>
        public void FillComboBox(ComboBox cmb, string strTmpSelectString, int iColumnIndex)
        {
            DataTable tmpdt = GetData(strTmpSelectString);
            cmb.Items.Clear();
            if (tmpdt.Rows.Count > 0)
            {
                for (int i = 0; i < tmpdt.Rows.Count; i++)
                {
                    if (tmpdt.Rows[i][iColumnIndex].Equals(DBNull.Value))
                    {
                        cmb.Items.Add("[NULL]");
                    }
                    else
                    {
                        cmb.Items.Add(tmpdt.Rows[i][iColumnIndex]);
                    }
                }
            }
        }

        /// <summary>
        /// 填充列表框
        /// </summary>
        /// <param name="lst">要填充的ListBox控件</param>
        /// <param name="strTmpSelectString">查询语句</param>
        /// <param name="iColumnIndex">要填充的列索引</param>
        public void FillListBox(ListBox lst, string strTmpSelectString,int iColumnIndex)
        {
            DataTable tmpdt = GetData(strTmpSelectString);
            lst.Items.Clear();
            if (tmpdt.Rows.Count > 0)
            {
                for (int i = 0; i < tmpdt.Rows.Count; i++)
                {
                    if (tmpdt.Rows[i][iColumnIndex].Equals(DBNull.Value))
                    {
                        lst.Items.Add("[NULL]");
                    }
                    else
                    {
                        lst.Items.Add(tmpdt.Rows[i][iColumnIndex]);
                    }
                }
            }
        }

        /// <summary>
        /// 查询记录是否存在
        /// </summary>
        /// <param name="strTmpCmdString">查询语句</param>
        /// <returns>是/否</returns>
        public bool RecordExist(string strTmpCmdString)
        {
            DataTable tmpdt= GetData(strTmpCmdString);
            return (tmpdt.Rows.Count > 0);
        }

        /// <summary>
        /// 查询服务器日期
        /// </summary>
        /// <returns>服务器日期字符串，格式为"YYYYMMDD"</returns>
        public string GetServerDate()
        {
            return GeneralTools.GetString(DoCmdScalarData(strSelectServerDate),string.Empty );
        }

        /// <summary>
        /// 显示错误信息
        /// </summary>
        /// <param name="strTmpMsg">错误信息</param>
        private void ShowErrMsg(string strTmpMsg)
        {
            MessageBox.Show(strTmpMsg, "错误", MessageBoxButtons.OK);
        }

        private string SqlDBTypeName(int Index)
        {
            string Result = "";

            switch (Index)
            {
                case 56: Result = "Int"; break;
                case 127: Result = "bigint"; break;
                case 173: Result = "binary"; break;
                case 104: Result = "bit"; break;
                case 175: Result = "char"; break;
                case 61: Result = "datetime"; break;
                case 106: Result = "decimal"; break;
                case 62: Result = "float"; break;
                case 34: Result = "image"; break;
                case 60: Result = "money"; break;
                case 239: Result = "nchar"; break;
                case 99: Result = "ntext"; break;
                case 108: Result = "numeric"; break;
                case 231: Result = "nvarchar"; break;
                case 59: Result = "real"; break;
                case 58: Result = "smalldatetime"; break;
                case 52: Result = "smallint"; break;
                case 122: Result = "smallmoney"; break;
                case 98: Result = "sql_variant"; break;
                case 35: Result = "text"; break;
                case 189: Result = "timestamp"; break;
                case 48: Result = "tinyint"; break;
                case 36: Result = "uniqueidentifier"; break;
                case 165: Result = "varbinary"; break;
                case 167: Result = "varchar"; break;
            }
            return (Result);
        }

        public class DataStruct
        {
            public int PrimaryKeyIndex;
            public List<FieldDetail> Fields;
            public int IdentifyIndex;

            public DataStruct()
            {
                Fields = new List<FieldDetail>();
                PrimaryKeyIndex = -1;
                IdentifyIndex = -1;
            }

            public int FindField(string strTmpFieldName)
            {
                int result = -1;
                if (Fields != null && Fields.Count > 0)
                {
                    for (int i = 0; i < Fields.Count; i++)
                    {
                        if (Fields[i].FieldName == strTmpFieldName)
                        {
                            result = i;
                            return result;
                        }
                    }
                }
                return result;
            }
        }

        public struct FieldDetail
        {
            public String FieldName;
            public SqlDbType FieldType;
            public int Length;
            public bool AllowNull;
            public object DefualtValue;
        }

        /// <summary>
        /// 获取SQL数据类型
        /// </summary>
        /// <param name="TypeName">类型名称</param>
        /// <returns>SQL数据类型</returns>
        private SqlDbType GetSqlDbType(string TypeName)
        {
            SqlDbType sqlDbType = new SqlDbType();
            foreach (string s in Enum.GetNames(sqlDbType.GetType()))
            {
                if (s.ToLower() == TypeName.ToLower())
                {
                    return (SqlDbType)Enum.Parse(sqlDbType.GetType(), s);
                }

            }
            return SqlDbType.VarChar;
        }

        /// <summary>
        /// 获取指定表结构（SQL2000不适用）
        /// </summary>
        /// <param name="TableName">要获取结构的表名</param>
        /// <returns>表结构</returns>
        public DataStruct GetTableStruct(string TableName)
        {
            StringBuilder SqlstripBuild = new StringBuilder();
            SqlstripBuild.AppendLine("SELECT 表名=CASE WHEN a.colorder = 1 THEN d.name ELSE '' END,");
            SqlstripBuild.AppendLine("表说明=CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END ,");
            SqlstripBuild.AppendLine("字段序号=a.colorder ,");
            SqlstripBuild.AppendLine("字段名=a.name ,");
            SqlstripBuild.AppendLine("标识=CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END ,");
            SqlstripBuild.AppendLine("主键=CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END ,");
            SqlstripBuild.AppendLine("外键=CASE WHEN tony.fkey is not null and tony.fkey=a.colid THEN '√' ELSE '' END ,");
            SqlstripBuild.AppendLine("外键表=CASE WHEN tony.fkey is not null and tony.fkey=a.colid THEN object_name(tony.fkeyid) ELSE '' END ,");
            SqlstripBuild.AppendLine("外键字段=CASE WHEN tony.fkey is not null and tony.fkey=a.colid THEN (SELECT name FROM syscolumns WHERE colid=tony.fkey AND id=tony.fkeyid) ELSE '' END,");
            SqlstripBuild.AppendLine("类型=b.name,   ");
            SqlstripBuild.AppendLine("长度=a.length ,");
            SqlstripBuild.AppendLine("精度=COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,");
            SqlstripBuild.AppendLine("小数位数=ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,");
            SqlstripBuild.AppendLine("允许空=CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END ,");
            SqlstripBuild.AppendLine("默认值= ISNULL(e.text, ''),");
            SqlstripBuild.AppendLine("字段说明=ISNULL(g.[value], ''),");
            SqlstripBuild.AppendLine("创建时间=d.crdate,");
            SqlstripBuild.AppendLine("更改时间=CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END");
            SqlstripBuild.AppendLine("FROM dbo.syscolumns a");
            SqlstripBuild.AppendLine("LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype");
            SqlstripBuild.AppendLine("INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0");
            SqlstripBuild.AppendLine("LEFT JOIN dbo.syscomments e ON a.cdefault = e.id");
            SqlstripBuild.AppendLine("LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id");
            SqlstripBuild.AppendLine("LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 ");
            SqlstripBuild.AppendLine("LEFT JOIN sysobjects htl ON htl.parent_obj=d.id AND htl.xtype='F'");
            SqlstripBuild.AppendLine("LEFT JOIN sysforeignkeys tony on htl.id=tony.constid");
            SqlstripBuild.AppendLine("WHERE d.name='" + TableName + "'");
            SqlstripBuild.AppendLine("ORDER BY d.id, a.colorder");
            DataTable tmpdt = GetData(SqlstripBuild.ToString());
            DataStruct tmpds = new DataStruct();
            for (int i = 0; i < tmpdt.Rows.Count; i++)
            {
                FieldDetail tmpfd = new FieldDetail();
                tmpfd.FieldName = tmpdt.Rows[i]["字段名"].ToString();
                tmpfd.FieldType = GetSqlDbType(tmpdt.Rows[i]["类型"].ToString());
                tmpfd.Length = int.Parse(tmpdt.Rows[i]["长度"].ToString());
                tmpfd.DefualtValue = tmpdt.Rows[i]["默认值"];
                if (tmpdt.Rows[i]["允许空"].ToString() == "√")
                {
                    tmpfd.AllowNull = true;
                }
                else
                {
                    tmpfd.AllowNull = false;
                }
                if (tmpdt.Rows[i]["主键"].ToString() == "√")
                {
                    tmpds.PrimaryKeyIndex = i;
                }
                if (tmpdt.Rows[i]["标识"].ToString() == "√")
                {
                    tmpds.IdentifyIndex = i;
                }
                tmpds.Fields.Add(tmpfd);
            }
            return (tmpds);
        }
        #endregion 方法
    }
}
